﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using log4net;
using ThreeByte.ActiveDeck.Config;
using ThreeByte.ActiveDeck.Service;
using System.Xml.Linq;
using PowerPoint = Microsoft.Office.Interop.PowerPoint;
using Excel = Microsoft.Office.Interop.Excel;
using ExcelConstants = Microsoft.Office.Interop.Excel.Constants;
using System.Runtime.InteropServices;
using ThreeByte.ActiveDeck.Data.UserResponse.Report;
using System.Drawing;

namespace ThreeByte.ActiveDeck.Data.UserResponse
{
    //Client side class
    public class ReportGenerator
    {
        private static readonly ILog log = LogManager.GetLogger(typeof(ReportGenerator));

        private static readonly XNamespace ns = ServiceConfiguration.NAMESPACE;

        private readonly WebServiceClient Client;
        private readonly CoreConfig Config;

        public ReportGenerator(CoreConfig config) {
            Config = config;
            Client = new WebServiceClient(config.PresenterLogin);
        }

        public XElement GetXmlReport() {

            XElement root = new XElement(ns + "UserData");

            XElement aggregateData = new XElement(ns + "AggregateData");

            root.Add(aggregateData);

            List<int> SlideIdsToUpload = new List<int>();

            foreach(PowerPoint.Slide slide in Config.Presentation.Slides) {
                //Verify whether these go in order....
                log.DebugFormat("Inventory Slide: {0} / {1}", slide.SlideNumber, slide.SlideID);
                if(Config.SlideIDs.Contains(slide.SlideID)) {
                    //See if this is a question slide we care about
                    if(HasUserResponseActivity(Config[slide.SlideID])) {
                        SlideIdsToUpload.Add(slide.SlideID);
                    }
                }
            }
            
            //For each configured question (in presentation order)
            foreach(int i in SlideIdsToUpload) {

                foreach(PageConfig page in Config[i].Pages) {
                    //Get the user data associated with it (or a note if there is none)
                    log.DebugFormat("Retrieve User Data: {0}.{1}", page.ParentSlideConfig.SlideId, page.PageId);
                    XElement data = Client.GetUserData(Config.ID, page.ParentSlideConfig.SlideId, page.PageId);
                    AggregateUserData userData = AggregateUserData.ParseXml(data.Element(ns + "Aggregate"));

                    AggregateUserData baseUserData = null;
                    if(page is QuestionConfig) {
                        baseUserData = ((QuestionConfig)page).InitUserData();
                    } else if(page is MultipleChoiceConfig) {
                        baseUserData = ((MultipleChoiceConfig)page).InitUserData();
                    } else if(page is CategoriesConfig) {
                        baseUserData = ((CategoriesConfig)page).InitUserData();
                    }
                    //Handle empty data.
                    if(baseUserData != null) {
                        baseUserData.UpdateUserData(userData);
                        //Add to the XML node
                        XElement dataInContext = page.GetReportContext(userData);
                        aggregateData.Add(dataInContext);
                    }
                }
            }


            root.Add(GetUpdatedUserDataReport(SlideIdsToUpload).ToXml());

            return root;
        }

        public bool GetXlsReport(string filePath)
        {
            //Create all my Excel COM Objects
            Excel.Application myApplication = new Excel.Application();
            Excel.Workbooks myWorkbooks = myApplication.Workbooks;
            Excel.Workbook myWorkbook = myWorkbooks.Add();
            Excel.Sheets myWorksheets = myWorkbook.Worksheets;

            //turn off alerts to suppress save as dialog
            myApplication.DisplayAlerts = false;

            //initialize a worksheet and range object
            Excel.Worksheet pageWorksheet = myWorksheets.Add();
            Excel.Range range = pageWorksheet.Range["A1", "E1"];
            
            //XElement root = new XElement(ns + "UserData");

            List<int> SlideIdsToUpload = new List<int>();

            foreach (PowerPoint.Slide slide in Config.Presentation.Slides)
            {
                //Verify whether these go in order....
                log.DebugFormat("Inventory Slide: {0} / {1}", slide.SlideNumber, slide.SlideID);
                if (Config.SlideIDs.Contains(slide.SlideID))
                {
                    //See if this is a question slide we care about
                    if (HasUserResponseActivity(Config[slide.SlideID]))
                    {
                        SlideIdsToUpload.Add(slide.SlideID);
                    }
                }
            }
            
            //For each configured question (in presentation order)
            foreach (int i in SlideIdsToUpload) {

                foreach (PageConfig page in Config[i].Pages) {
                    //Get the user data associated with it (or a note if there is none)
                    log.DebugFormat("Retrieve User Data: {0}.{1}", page.ParentSlideConfig.SlideId, page.PageId);
                    pageWorksheet = (Excel.Worksheet)myWorksheets.Add();
                    pageWorksheet.Name = page.Name;
                    XElement data = Client.GetUserData(Config.ID, page.ParentSlideConfig.SlideId, page.PageId);
                    AggregateUserData userData = AggregateUserData.ParseXml(data.Element(ns + "Aggregate"));

                    //Create header
                    //range = pageWorksheet.Range["B2", "F2"];
                    //range.MergeCells = true;

                    AggregateUserData baseUserData = null;
                    range = pageWorksheet.Range["B2"];

                    range.Font.Bold = true;
                    range.Font.Color = Color.FromArgb(155, 187, 89);

                    range.MergeCells = false;
                    if (page is QuestionConfig) {
                        baseUserData = ((QuestionConfig)page).InitUserData();
                        baseUserData.UpdateUserData(userData);
                        ShortAnswerUserData questionUserData = ((ShortAnswerUserData)baseUserData);
                        range.Value = ((QuestionConfig)page).Question;
                        int a = 3;

                        foreach (string answer in questionUserData.Answers) {
                            range = pageWorksheet.Range["B" + a.ToString()];
                            range.Value = answer;
                            a++;
                        }
                       
                        range = pageWorksheet.Range["B3", "B" + (a-1).ToString()];
                        range.Font.Color = Color.FromArgb(192, 80, 77);

                        range = pageWorksheet.UsedRange;
                        range.HorizontalAlignment = ExcelConstants.xlLeft;
                    } else if (page is MultipleChoiceConfig) {
                        baseUserData = ((MultipleChoiceConfig)page).InitUserData();
                        baseUserData.UpdateUserData(userData);
                        MultipleChoiceUserData multipleChoiceUserData = ((MultipleChoiceUserData)baseUserData);
                        MultipleChoiceConfig mConfig = (MultipleChoiceConfig)page;

                        range.Value = ((MultipleChoiceConfig)page).Question;
                        int a = 3;
                        foreach (MultipleChoiceAnswerData answer in multipleChoiceUserData.Answers) {
                            
                            range = pageWorksheet.Range["B" + a.ToString()];
                            range.Value = mConfig.Answers[answer.Value - 1].Text;
    
                            range = pageWorksheet.Range["C" + a.ToString()];
                            range.Value = answer.UserResponses;
                            a++;

                        }

                        range = pageWorksheet.Range["B3", "C" + (a - 1).ToString()];
                        range.Font.Color = Color.FromArgb(192, 80, 77);

                        range = pageWorksheet.Range["B3", "B" + (a - 1).ToString()];
                        range.HorizontalAlignment = ExcelConstants.xlLeft;

                        range = pageWorksheet.Range["C3", "C" + (a - 1).ToString()];
                        range.HorizontalAlignment = ExcelConstants.xlCenter;

                    } else if (page is CategoriesConfig) {
                        baseUserData = ((CategoriesConfig)page).InitUserData();
                        baseUserData.UpdateUserData(userData);
                        CategoriesConfig cConfig = (CategoriesConfig)page;
                        CategoriesUserData categoriesUserData = ((CategoriesUserData)baseUserData);
                        
                        //Put the question at the top of the sheet
                        range.Value = ((CategoriesConfig)page).Question;                        

                        //Maps Word ids to a list of the counts for each category
                        Dictionary<int, int[]> catItemValueArray = new Dictionary<int, int[]>();

                        //For each category display the category name and add the data to the dictionary
                        int colNum = 3; //3 is for the letter C.
                        foreach (CategoryData category in categoriesUserData.Categories) {

                            range = pageWorksheet.Range[GetExcelColumnName(colNum) + "3"];
                            range.Value = cConfig.Categories[category.ID - 1].Text;

                            foreach (CategoryItemValue catItemValue in category.ItemValues) {
                                if (!catItemValueArray.ContainsKey(catItemValue.ID)) {
                                    catItemValueArray[catItemValue.ID] = new int[categoriesUserData.Categories.Count];
                                }
                                catItemValueArray[catItemValue.ID][category.ID - 1] = catItemValue.Responses;
                            }
                            colNum++;
                        }

                        range = pageWorksheet.Range[GetExcelColumnName(3) + "3", GetExcelColumnName(colNum - 1) + "3"];
                        range.Font.Bold = true;
                        range.Font.Color = Color.FromArgb(192, 80, 77);

                        // for each word in the dictionary, add the word to the leftmost column
                        int a = 4;
                        foreach (int w in catItemValueArray.Keys.OrderBy(p => p)) {

                            range = pageWorksheet.Range["B" + a.ToString()];
                            range.Value = cConfig.Words[w - 1].Text;
                            a++;
                        }

                        range = pageWorksheet.Range["B4", "B" + (a - 1).ToString()];
                        range.Font.Bold = true;

                        // for each category get all the word response totals
                        colNum = 3; //3 is for the letter 3.  Need to increment by letter
                        foreach (CategoryData category in categoriesUserData.Categories) {
                            a = 4;
                            foreach (int w in catItemValueArray.Keys) {
                                range = pageWorksheet.Range[GetExcelColumnName(colNum) + a.ToString()];
                                range.Value = catItemValueArray[w][category.ID - 1];

                                a++;
                            }
                            colNum++;
                        }

                        range = pageWorksheet.Range["B4", GetExcelColumnName(colNum - 1) + (a - 1).ToString()];
                        range.Font.Color = Color.FromArgb(192, 80, 77);
                    }

                    range = pageWorksheet.UsedRange;
                    range.EntireColumn.AutoFit();

                    myWorksheets.Add(pageWorksheet);
                }
            }

            UserDataReport userDataReport = GetUpdatedUserDataReport(SlideIdsToUpload);

            Excel.Worksheet reportWorksheet = myWorksheets.Add();
            Excel.Range reportRange = reportWorksheet.Range["A2"];

            reportWorksheet.Name = "Grading Report";
            reportRange.Value = "Name";

            int mcColor = ColorTranslator.ToOle(Color.Green);
            int catColor = ColorTranslator.ToOle(Color.Aqua);
            int qColor = ColorTranslator.ToOle(Color.Fuchsia);

            float numberOfQuestions = 0;
            int questionColumn = 2; //column 2 = B

            //Set up all the questions at the top of the sheet
            foreach(int i in SlideIdsToUpload) {
                foreach(PageConfig page in Config[i].Pages) {
                    reportRange = reportWorksheet.Range[GetExcelColumnName(questionColumn) + "2"];
                    reportRange.HorizontalAlignment = ExcelConstants.xlCenter;
                    reportRange.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
                    reportRange.Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
                    reportRange.Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
                    reportRange.Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous;
                    if(page is MultipleChoiceConfig) {
                        numberOfQuestions++;
                        reportRange.Value = ((MultipleChoiceConfig)page).Question;
                        reportRange.Interior.Color = mcColor;
                        questionColumn++;
                    }

                    if(page is QuestionConfig) {
                        numberOfQuestions++;
                        reportRange.Value = ((QuestionConfig)page).Question;
                        reportRange.Interior.Color = qColor;
                        questionColumn++;
                    }

                    if(page is CategoriesConfig) {
                        CategoriesConfig cConfig = (CategoriesConfig)page;
                        reportRange = reportWorksheet.Range[GetExcelColumnName(questionColumn) + "1", GetExcelColumnName(questionColumn + cConfig.Words.Count - 1) + "1"];
                        reportRange.Merge();
                        reportRange.HorizontalAlignment = ExcelConstants.xlCenter;
                        reportRange.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
                        reportRange.Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
                        reportRange.Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
                        reportRange.Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous;
                        reportRange.Value = cConfig.Prompt;
                        reportRange.Interior.Color = catColor;
                        
                        foreach(Word w in cConfig.Words) {
                            numberOfQuestions++;
                            reportRange = reportWorksheet.Range[GetExcelColumnName(questionColumn) + "2"];
                            reportRange.HorizontalAlignment = ExcelConstants.xlCenter;
                            reportRange.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
                            reportRange.Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
                            reportRange.Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
                            reportRange.Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous;
                            reportRange.Value = w.Text;
                            reportRange.Interior.Color = catColor;
                            questionColumn++;
                        }
                    }                    
                }
            }

            reportRange = reportWorksheet.Range[GetExcelColumnName(questionColumn) + "2"];
            reportRange.Value = "% Correct";

            reportRange = reportWorksheet.Range["A1", GetExcelColumnName(questionColumn) + "2"];
            reportRange.Font.Bold = true;
            reportRange = reportWorksheet.Range["A2", GetExcelColumnName(questionColumn) + "2"];
            reportRange.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
            reportRange.Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = Excel.XlBorderWeight.xlThick;


            int reportRow = 3;
            Excel.Range questionRange = reportWorksheet.Range["B2", GetExcelColumnName(questionColumn) + "2"]; 
             
            //populate the answers and correctness
            foreach(IndividualResponseData ird in userDataReport.ResponseDataElements) {
                float numberCorrect = 0;
                reportRange = reportWorksheet.Range["A" + reportRow.ToString()];
                reportRange.Value = ird.UserName;

                questionRange = reportWorksheet.Range["B2", GetExcelColumnName(questionColumn) + "2"];
                foreach(IndividualUserData iud in ird.UserDataElements) {
                    if(iud is MultipleChoiceIndividualData || iud is ShortAnswerIndividualData) {
                        questionRange = questionRange.Find(iud.Question);
                        if(iud.Question == questionRange.Value) {
                            reportRange = reportWorksheet.Cells[reportRow, questionRange.Column];
                            if(iud is MultipleChoiceIndividualData) {
                                MultipleChoiceIndividualData mData = (MultipleChoiceIndividualData)iud;
                                reportRange.Value = mData.TextAnswer;
                                if(mData.IsCorrect) {
                                    numberCorrect++;
                                    reportRange.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Green);
                                } else {
                                    reportRange.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
                                }
                            }

                            if(iud is ShortAnswerIndividualData) {
                                ShortAnswerIndividualData saData = (ShortAnswerIndividualData)iud;
                                reportRange.Value = saData.Answer;
                                if(saData.IsCorrect) {
                                    numberCorrect++;
                                    reportRange.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Green);
                                } else {
                                    reportRange.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
                                }
                            }
                        }
                    }

                    if(iud is CategoriesIndividualData) {
                        CategoriesIndividualData cData = (CategoriesIndividualData)iud;
                        foreach(Tuple<CategoriesIndividualData.Word, CategoriesIndividualData.Category> catWord in cData.CategoriesWords) {
                            questionRange = questionRange.Find(catWord.Item1.Text);
                            reportRange = reportWorksheet.Cells[reportRow, questionRange.Column];
                            reportRange.Value = catWord.Item2.Text;
                            if(catWord.Item1.IsCorrect) {
                                numberCorrect++;
                                reportRange.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Green);
                            } else {
                                reportRange.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
                            }
                        }
                    }
                }

                reportRange = reportWorksheet.Range[GetExcelColumnName(questionColumn) + reportRow.ToString()];
                reportRange.Value = (numberCorrect / numberOfQuestions);
                reportRange.NumberFormat = "##0.00%";

                reportRow++;
            }

            reportRange = reportWorksheet.UsedRange;
            reportRange.EntireColumn.AutoFit();

            myWorksheets.Add(reportWorksheet);

            foreach (Excel.Worksheet ws in myWorksheets) {
                if (ws.Name.StartsWith("Sheet"))
                    ws.Delete();
            }

            bool wasSaved = false;
            try {
                myWorkbook.SaveAs(filePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Excel.XlSaveConflictResolution.xlLocalSessionChanges, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                wasSaved = true;
            } catch (Exception ex) {
                log.Error("Could not save excel file", ex);
                wasSaved = false;
            }

            //Release all the COM objects
            Marshal.FinalReleaseComObject(questionRange);
            Marshal.FinalReleaseComObject(reportRange);
            Marshal.FinalReleaseComObject(range);
            Marshal.FinalReleaseComObject(reportWorksheet);
            Marshal.FinalReleaseComObject(pageWorksheet);
            Marshal.FinalReleaseComObject(myWorksheets);
            
            myWorkbook.Close();
            Marshal.FinalReleaseComObject(myWorkbook);
            myWorkbooks.Close();
            Marshal.FinalReleaseComObject(myWorkbooks);
            myApplication.Quit();
            Marshal.FinalReleaseComObject(myApplication);

            pageWorksheet = null;
            range = null;
            questionRange = null;
            reportRange = null;
            reportWorksheet = null;
            myWorksheets = null;
            myWorkbook = null;
            myWorkbooks = null;
            myApplication = null;

            //Convert to to human readable/interpretable data
            return wasSaved;
        }

        //Converts a number into the proper column letter
        public static string GetExcelColumnName(int colNumber) {
            int dividend = colNumber;
            string columnName = "";
            int modulo;

            while(dividend > 0) {
                modulo = (dividend - 1) % 26;
                columnName = Convert.ToChar(65 + modulo).ToString();
                dividend = (int)((dividend - modulo) / 26);
            }

            return columnName;
        }

        private UserDataReport GetUpdatedUserDataReport(List<int> SlideIdsToUpload) {
            //Get the user Data report
            UserDataReport userDataReport = UserDataReport.ParseXml(Client.GetUserDataReport(Config.ID));

            if(userDataReport.ResponseDataElements.Count > 0) {

                foreach(int i in SlideIdsToUpload) {
                    foreach(PageConfig page in Config[i].Pages) {
                        foreach(IndividualResponseData ird in userDataReport.ResponseDataElements) {
                            
                            foreach(IndividualUserData iud in ird.UserDataElements) {
                                if(page is MultipleChoiceConfig) {
                                    if(page.PageId == iud.Page && Config.GetSectionForPageId(page.PageId) == iud.Section) {
                                        if(iud is MultipleChoiceIndividualData) {
                                            MultipleChoiceIndividualData mData = (MultipleChoiceIndividualData)iud;
                                            MultipleChoiceConfig mConfig = (MultipleChoiceConfig)page;

                                            iud.Question = mConfig.Question;

                                            mData.IsCorrect = mConfig.Answers[mData.Answer - 1].IsCorrect;

                                            mData.TextAnswer = mConfig.Answers[mData.Answer - 1].Text;
                                        }
                                    }
                                } else if(page is CategoriesConfig) {
                                    if(page.PageId == iud.Page && Config.GetSectionForPageId(page.PageId) == iud.Section) {
                                        if(iud is CategoriesIndividualData) {
                                            CategoriesIndividualData cData = (CategoriesIndividualData)iud;
                                            CategoriesConfig cConfig = (CategoriesConfig)page;

                                            iud.Question = cConfig.Prompt;
                                            foreach(Tuple<CategoriesIndividualData.Word, CategoriesIndividualData.Category> catWord in cData.CategoriesWords) {

                                                catWord.Item1.Text = cConfig.Words[catWord.Item1.ID - 1].Text;
                                                catWord.Item2.Text = cConfig.Categories[catWord.Item2.ID - 1].Text;

                                                if(cConfig.Words[catWord.Item1.ID - 1].CorrectCategory != null) {
                                                    catWord.Item1.IsCorrect = (cConfig.Words[catWord.Item1.ID - 1].CorrectCategory.Text == catWord.Item2.Text);
                                                }
                                            }
                                        }
                                    }
                                } else if(page is QuestionConfig) {
                                    if(page.PageId == iud.Page && Config.GetSectionForPageId(page.PageId) == iud.Section) {
                                        if(iud is ShortAnswerIndividualData) {
                                            QuestionConfig qConfig = (QuestionConfig)page;
                                            ShortAnswerIndividualData saData = (ShortAnswerIndividualData)iud;

                                            saData.IsCorrect = (saData.Answer == qConfig.Answer);

                                            saData.Question = qConfig.Question;
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
            }
            
            return userDataReport;
        }


        //TODO: Do something cooler with reflection
        private static bool HasUserResponseActivity(SlideConfig slideConfig) {
            foreach(PageConfig page in slideConfig.Pages) {
                if(page is QuestionConfig
                    || page is MultipleChoiceConfig
                    || page is CategoriesConfig) {
                    return true;
                }
            }

            return false;
        }

    }
}
